{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Alvald1/HSE_colab/blob/main/Final_Test.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Cmcl39_OqoYu"
      },
      "source": [
        "#### 0.1 Загрузите библиотеки"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "id": "32qiYyf0qoYx"
      },
      "outputs": [],
      "source": [
        "import numpy as np \n",
        "import sqlite3\n",
        "import pandas as pd"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xd2hP2LnqoYy"
      },
      "source": [
        "#### 1.1 Как объединить два массива Numpy?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MZ-dYuzwqoYz",
        "outputId": "aa21d421-efb8-4bfe-86de-fbb4a794df42"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array([[ 1,  2,  3,  8,  9, 10]])"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ],
      "source": [
        "a=np.array([[1,2,3]])\n",
        "b=np.array([[8,9,10]])\n",
        "\n",
        "c = np.concatenate((a, b), axis=1)\n",
        "\n",
        "c"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Uy7noNdMqoYz"
      },
      "source": [
        "#### 1.2 Напишите код на языке \"Python\" для получения индексов из 3 максимальных значений в массиве NumPy.\n",
        "Пример:\n",
        "\n",
        "Input: a = np.array([1, 3, 2, 4, 5])\n",
        "\n",
        "Output: [4 3 1]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nSil2XcGqoYz",
        "outputId": "653151ea-1da6-48c8-fc1a-aacd63b5fc4a"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "4\n"
          ]
        }
      ],
      "source": [
        "a = np.array([1, 3, 2, 4, 5])\n",
        "\n",
        "print(np.argmax(a))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U1jiKuCRqoY0"
      },
      "source": [
        "#### 1.3 Создайте массив размерностью \"10x10\" со случайными значениями и найдите минимум и максимум в этом массиве"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 11,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "9ZhhnKd0qoY1",
        "outputId": "6559d1cc-6dbb-454d-b48c-f859e1dbd0a7"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[[62  7 78 92 64 74  7 22 16 60]\n",
            " [59 64 45 13 73 60 14 55 99 33]\n",
            " [11 56 81 14 61 35 33 55 74 75]\n",
            " [48 11 13 46 29 81 73  0 65 23]\n",
            " [42 73 49  5 67 41 97 55 52 30]\n",
            " [62 78 64 25 37 76 45 62 18 15]\n",
            " [84 96 20 16 98 54  7 28 73 82]\n",
            " [32  0 97 56 59 12  2 61 54  1]\n",
            " [68 14 99 52 44 28 38 66 24 66]\n",
            " [94  7 21 53 16 73 43 73 70 33]]\n",
            "99\n",
            "0\n"
          ]
        }
      ],
      "source": [
        "data = np.random.randint(0, 100, (10, 10))\n",
        "print(data)\n",
        "print(np.max(data))\n",
        "print(np.min(data))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3HvkDnQFqoY1"
      },
      "source": [
        "#### 1.4 Создать \"8x8\" матрицу и заполнить её в шахматном порядке\n",
        "Подсказка: Можно сделать через функцию \"tile\""
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 13,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "c-0rNtbhqoY2",
        "outputId": "b9f6b9ae-3e3a-4370-9cb6-c7166617df96"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array([[0, 1, 0, 1, 0, 1, 0, 1],\n",
              "       [1, 0, 1, 0, 1, 0, 1, 0],\n",
              "       [0, 1, 0, 1, 0, 1, 0, 1],\n",
              "       [1, 0, 1, 0, 1, 0, 1, 0],\n",
              "       [0, 1, 0, 1, 0, 1, 0, 1],\n",
              "       [1, 0, 1, 0, 1, 0, 1, 0],\n",
              "       [0, 1, 0, 1, 0, 1, 0, 1],\n",
              "       [1, 0, 1, 0, 1, 0, 1, 0]])"
            ]
          },
          "metadata": {},
          "execution_count": 13
        }
      ],
      "source": [
        "\n",
        "np.fromfunction(lambda i,j: (i + j) % 2, (8, 8), dtype=int)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "sxM8lpodqoY2"
      },
      "source": [
        "#### 1.5 Отнять среднее из каждой строки в матрице состоящей из случайных чисел"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "peE6LwoJqoY2",
        "outputId": "dab9b2c8-6ee3-481b-8961-0ccf1e0d6de3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[[96  1 65 68 49 35 41 52 87  9]\n",
            " [15 94 44 82 89 13 28 64 48 67]\n",
            " [88 22 44 23 94 38 35 21 28 26]\n",
            " [62 64 68 35 55  3 92 71 39 12]\n",
            " [ 6 39 72 25 66 49 48 17 60 79]\n",
            " [ 6 93 26 43 32 36 31 84 40 77]\n",
            " [54 11 58 37 80  0 56 99 44 78]\n",
            " [10 95 48 82 11  9 90 75 95 14]\n",
            " [61 32 93 43 95  9  1 46 34 68]\n",
            " [48 23 70 20  9 33 29 62 56 12]]\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array([[ 48.14, -46.86,  17.14,  20.14,   1.14, -12.86,  -6.86,   4.14,\n",
              "         39.14, -38.86],\n",
              "       [-32.86,  46.14,  -3.86,  34.14,  41.14, -34.86, -19.86,  16.14,\n",
              "          0.14,  19.14],\n",
              "       [ 40.14, -25.86,  -3.86, -24.86,  46.14,  -9.86, -12.86, -26.86,\n",
              "        -19.86, -21.86],\n",
              "       [ 14.14,  16.14,  20.14, -12.86,   7.14, -44.86,  44.14,  23.14,\n",
              "         -8.86, -35.86],\n",
              "       [-41.86,  -8.86,  24.14, -22.86,  18.14,   1.14,   0.14, -30.86,\n",
              "         12.14,  31.14],\n",
              "       [-41.86,  45.14, -21.86,  -4.86, -15.86, -11.86, -16.86,  36.14,\n",
              "         -7.86,  29.14],\n",
              "       [  6.14, -36.86,  10.14, -10.86,  32.14, -47.86,   8.14,  51.14,\n",
              "         -3.86,  30.14],\n",
              "       [-37.86,  47.14,   0.14,  34.14, -36.86, -38.86,  42.14,  27.14,\n",
              "         47.14, -33.86],\n",
              "       [ 13.14, -15.86,  45.14,  -4.86,  47.14, -38.86, -46.86,  -1.86,\n",
              "        -13.86,  20.14],\n",
              "       [  0.14, -24.86,  22.14, -27.86, -38.86, -14.86, -18.86,  14.14,\n",
              "          8.14, -35.86]])"
            ]
          },
          "metadata": {},
          "execution_count": 16
        }
      ],
      "source": [
        "data = np.random.randint(0, 100, (10, 10))\n",
        "print(data)\n",
        "tmp = np.average(data)\n",
        "data=data-tmp\n",
        "data"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Tru_sdxOqoY3"
      },
      "source": [
        "#### 1.6* Дан массив 16x16, посчитать сумму по блокам 4x4"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a9gKwszSqoY3",
        "outputId": "1ecdddb5-ac91-4cff-822f-f5e601d0fcc5"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[[5 0 7 2 8 4 2 7 6 3 8 2 0 0 5]\n",
            " [9 9 5 9 9 1 5 9 8 9 8 2 9 2 7]\n",
            " [5 7 3 1 6 1 0 8 7 5 9 8 8 1 2]\n",
            " [6 6 7 5 6 0 8 2 2 6 4 9 7 2 4]\n",
            " [0 9 1 9 2 0 6 9 3 5 8 9 8 1 0]\n",
            " [7 9 0 2 3 7 3 9 1 6 6 7 8 5 8]\n",
            " [0 6 4 1 2 4 2 3 4 6 8 5 8 6 7]\n",
            " [2 7 5 5 6 6 1 6 0 9 7 2 1 1 8]\n",
            " [9 4 4 0 2 5 7 7 2 8 1 4 6 2 9]\n",
            " [7 8 0 2 9 5 7 1 1 0 4 4 6 0 6]\n",
            " [5 2 1 6 7 1 8 6 9 4 7 3 0 0 3]\n",
            " [7 6 9 1 2 7 7 6 2 3 4 3 5 4 3]\n",
            " [6 3 1 5 2 3 5 1 6 0 6 8 8 8 4]\n",
            " [8 3 8 6 2 0 7 1 3 4 8 8 7 4 5]\n",
            " [5 7 4 8 5 9 9 8 1 9 5 3 2 1 4]\n",
            " [6 0 0 5 7 4 7 5 4 4 1 2 6 2 5]]\n",
            "[[5 0 7 2]\n",
            " [9 9 5 9]\n",
            " [5 7 3 1]\n",
            " [6 6 7 5]]\n",
            "[[2 0 6 9]\n",
            " [3 7 3 9]\n",
            " [2 4 2 3]\n",
            " [6 6 1 6]]\n"
          ]
        }
      ],
      "source": [
        "data = np.random.randint(0, 10, (16, 15))\n",
        "print(data)\n",
        "print(data[:4,:4])\n",
        "\n",
        "print(data[4:8,4:8])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "htEYIYR3qoY4"
      },
      "source": [
        "#### 2.1 Как создать Pandas series?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "m5LrVdQEqoY4",
        "outputId": "b03cc123-e237-4a1d-c923-0f087e1ea919"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "a    1\n",
            "b    2\n",
            "c    3\n",
            "dtype: int64\n"
          ]
        }
      ],
      "source": [
        "d = {'a': 1, 'b': 2, 'c': 3}\n",
        "ser = pd.Series(data=d, index=['a', 'b', 'c'])\n",
        "print(ser)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "etEZvghYqoY4"
      },
      "source": [
        "#### 2.2 Как создать Pandas dataframe?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 29,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "id": "pUBxouEmqoY4",
        "outputId": "67077c94-8e6b-40b8-9153-f02bf8e905d8"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "        1      2      3      4      5\n",
              "0      id  10001  10002  10003  10004\n",
              "1   class      4      4      5      5\n",
              "2  person    237    355    211    198"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-fcf3146b-593f-4267-8f8f-8b5d67c36d18\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>1</th>\n",
              "      <th>2</th>\n",
              "      <th>3</th>\n",
              "      <th>4</th>\n",
              "      <th>5</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>id</td>\n",
              "      <td>10001</td>\n",
              "      <td>10002</td>\n",
              "      <td>10003</td>\n",
              "      <td>10004</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>class</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "      <td>5</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>person</td>\n",
              "      <td>237</td>\n",
              "      <td>355</td>\n",
              "      <td>211</td>\n",
              "      <td>198</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-fcf3146b-593f-4267-8f8f-8b5d67c36d18')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-fcf3146b-593f-4267-8f8f-8b5d67c36d18 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-fcf3146b-593f-4267-8f8f-8b5d67c36d18');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 29
        }
      ],
      "source": [
        "df = pd.read_excel('FT_1.xlsx')\n",
        "df.head(3)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JyUO7mbrqoY5"
      },
      "source": [
        "#### 2.3 Создайте датафрейм со следующими значениями столбцов:\n",
        "\"A\": [420, 380, 390]\n",
        "\"B\": [50, 40, 45]\n",
        "\n",
        "Посчитайте столбец \"C\", в котором будет произведение поэлементно из \"A\" и \"B\":\n",
        "\n",
        "\"C\": [21000,15200,17550]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "dPFyKr7GqoY5",
        "outputId": "77a05de1-9c0d-47c2-ee8b-f20b735f70da"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "A          [420, 380, 390]\n",
            "B             [50, 40, 45]\n",
            "C    [21000, 15200, 17550]\n",
            "dtype: object\n"
          ]
        }
      ],
      "source": [
        "A=np.array([420, 380, 390])\n",
        "B=np.array([50, 40, 45])\n",
        "d = {\"A\": A ,  \"B\": B, 'C': np.array([])}\n",
        "ser = pd.Series(data=d, index=['A', 'B', 'C'])\n",
        "ser['C']=ser['A']*ser['B']\n",
        "print(ser)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N7TIRllrqoY5"
      },
      "source": [
        "#### 2.4 Создайте датафрейм с 5 атрибутами, состоящими из рандомных чисел от 100 до 1000 и посчитайте сумму, максимальный и минимальный элементы по строкам и столбцам."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lLUgpICdqoY5"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-aWB8QynqoY5"
      },
      "source": [
        "#### 2.5 У вас есть \"строка\" создать из нее датафрейм и разделить ее на два атрибута (столбца) - \"code\" и \"location\"  \n",
        "{'row': ['00000 UNITED STATES', '01000 ALABAMA', \n",
        "                           '01001 Autauga County, AL', '01003 Baldwin County, AL', \n",
        "                           '01005 Barbour County, AL']}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "metadata": {
        "id": "c-6B7fsGqoY6"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "af8sJwnyqoY6"
      },
      "source": [
        "##### Результат"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "metadata": {
        "id": "JnxuUKaIqoY6"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EvQZ-WoeqoY7"
      },
      "source": [
        "#### 2.6 Опишите каждую строку в функции describe"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 36,
      "metadata": {
        "id": "oOPqAVzGqoY7"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "78kXwEZtqoY7"
      },
      "source": [
        "#### 2.7* Опишите все виды соединений (слияний) в Pandas dataframe (и покажите каждый)**"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Sk5Pz0XVqoY7"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YHguHxunqoY7"
      },
      "source": [
        "#### 3.1 Создайте подключение к базе данных"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 40,
      "metadata": {
        "id": "qi98LEo5qoY7"
      },
      "outputs": [],
      "source": [
        "conn = sqlite3.connect(':memory:') # или :memory: чтобы сохранить в RAM\n",
        "cursor = conn.cursor()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iG_WLkmtqoY8"
      },
      "source": [
        "#### 3.2 Создайте таблицу с id INTEGER PRIMARY KEY и 5 атрибутами(столбцами) в этой базе, заполните ее 5 строчками"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 46,
      "metadata": {
        "id": "wmuyoo4XqoY8"
      },
      "outputs": [],
      "source": [
        "cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS tab\n",
        "                  (id INTEGER PRIMARY KEY , a TEXT, b TEXT, c TEXT, d TEXT, e TEXT)\n",
        "               \"\"\")\n",
        "\n",
        "albums = [('Pink Floyd', 'The Wall', 'Columbia Records', '23', '197'),\n",
        "          ('Pink Floyd', 'The Wall', 'Columbia Records', '23', '197'),\n",
        "          ('Pink Floyd', 'The Wall', 'Columbia Records', '23', '197'),\n",
        "          ('Pink Floyd', 'The Wall', 'Columbia Records', '23', '197'),\n",
        "          ('Pink Floyd', 'The Wall', 'Columbia Records', '23', '197')]\n",
        "cursor.executemany(\n",
        "    \"INSERT INTO tab(a, b, c, d, e) VALUES (?,?,?,?,?)\", \n",
        "    albums\n",
        ")\n",
        "conn.commit()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "seN22ixTqoY8"
      },
      "source": [
        "#### 3.3 Обновите одну из ранее вставленных строчек "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 48,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "OCPmGU1IqoY8",
        "outputId": "6f459640-d6fc-4e56-b057-c2cc52e72bbd"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<sqlite3.Cursor at 0x7f1b64ee7880>"
            ]
          },
          "metadata": {},
          "execution_count": 48
        }
      ],
      "source": [
        "cursor.execute(\"UPDATE tab SET a=? WHERE id=?\",('1970', 3))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pzEvIItdqoY9"
      },
      "source": [
        "#### 3.4 Добавте эти же строчки через pandas dataframe"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OgMFNPg8qoY9"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BEfVVE4xqoY9"
      },
      "source": [
        "#### 3.5 Покажите возможности агрегирующих функций (2-3 любых запроса с ними)\n",
        "\n",
        "---\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 55,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0RYhaAlyqoY9",
        "outputId": "87228143-9ec5-45a9-c9b2-71065f266279"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(2,)\n",
            "(0,)\n",
            "(11476,)\n"
          ]
        }
      ],
      "source": [
        "cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS tab2\n",
        "                  (id INTEGER PRIMARY KEY , a integer, b integer, c integer, d integer, e integer)\n",
        "               \"\"\")\n",
        "\n",
        "a=[(324,124,674,124,65),\n",
        "   (324,124,300,124,65),\n",
        "   (324,124,2,124,65),\n",
        "   (324,124,674,124,65),\n",
        "   (324,124,1,124,65)]\n",
        "\n",
        "cursor.executemany(\n",
        "    \"INSERT INTO tab2(a, b, c, d, e) VALUES (?,?,?,?,?)\", \n",
        "    a\n",
        ")\n",
        "conn.commit()\n",
        "\n",
        "cursor.execute(\"\"\"\n",
        "SELECT max(c) FROM tab2 \n",
        "WHERE c<=200\"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()\n",
        "print(SQLres)\n",
        "\n",
        "cursor.execute(\"\"\"\n",
        "SELECT min(c) FROM tab2 \"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()\n",
        "print(SQLres)\n",
        "\n",
        "cursor.execute(\"\"\"\n",
        "SELECT sum(c) FROM tab2 \"\"\" )\n",
        "\n",
        "SQLres=cursor.fetchone()\n",
        "print(SQLres)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I-XtEtx0qoY-"
      },
      "source": [
        "#### 3.6 Продемонстрируйте возможности оператора case-when-then"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 57,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 246
        },
        "id": "yN4XDEm7qoY-",
        "outputId": "0a9f9f4e-5e37-4748-b703-5ce9551a3f68"
      },
      "outputs": [
        {
          "output_type": "error",
          "ename": "OperationalError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-57-1d700ec95f7f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m cursor.execute(\"\"\"SELECT a, b,\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mCASE\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m     \u001b[0mWHEN\u001b[0m \u001b[0mc\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m30\u001b[0m \u001b[0mTHEN\u001b[0m \u001b[0;34m'The quantity is greater than 30'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m     \u001b[0mWHEN\u001b[0m \u001b[0mc\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;36m30\u001b[0m \u001b[0mTHEN\u001b[0m \u001b[0;34m'The quantity is 30'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m     \u001b[0mELSE\u001b[0m \u001b[0;34m'The quantity is under 30'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mOperationalError\u001b[0m: near \"FROM\": syntax error"
          ]
        }
      ],
      "source": [
        "cursor.execute(\"\"\"SELECT a, b,\n",
        "CASE\n",
        "    WHEN c > 30 THEN 'The quantity is greater than 30'\n",
        "    WHEN c = 30 THEN 'The quantity is 30'\n",
        "    ELSE 'The quantity is under 30'\n",
        "FROM tab2\"\"\")\n",
        "\n",
        "SQLres=cursor.fetchall()\n",
        "print(SQLres)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "9FKffWmD2ElN"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cZ3qsu5mqoY-"
      },
      "source": [
        "#### 3.7* Транспонируйте таблицу по атрибутам"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "beac8IlOqoY-",
        "outputId": "99bedd46-80d1-481e-8b37-5869a080ab4b"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>1</th>\n",
              "      <th>2</th>\n",
              "      <th>3</th>\n",
              "      <th>4</th>\n",
              "      <th>5</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>id</td>\n",
              "      <td>10001</td>\n",
              "      <td>10002</td>\n",
              "      <td>10003</td>\n",
              "      <td>10004</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>class</td>\n",
              "      <td>4</td>\n",
              "      <td>4</td>\n",
              "      <td>5</td>\n",
              "      <td>5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>person</td>\n",
              "      <td>237</td>\n",
              "      <td>355</td>\n",
              "      <td>211</td>\n",
              "      <td>198</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>gender</td>\n",
              "      <td>\"M\"</td>\n",
              "      <td>\"Ж\"</td>\n",
              "      <td>\"Ж\"</td>\n",
              "      <td>\"М\"</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>4</td>\n",
              "      <td>number</td>\n",
              "      <td>79602336578</td>\n",
              "      <td>79602336578</td>\n",
              "      <td>79802931277</td>\n",
              "      <td>79802931277</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        1            2            3            4            5\n",
              "0      id        10001        10002        10003        10004\n",
              "1   class            4            4            5            5\n",
              "2  person          237          355          211          198\n",
              "3  gender          \"M\"          \"Ж\"          \"Ж\"          \"М\"\n",
              "4  number  79602336578  79602336578  79802931277  79802931277"
            ]
          },
          "execution_count": 7,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "df = pd.read_excel('FT_1.xlsx')\n",
        "df"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iI-OP6J6qoY-",
        "outputId": "109cbac1-f2bb-44a0-9f34-53d903db0621"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>id</th>\n",
              "      <th>class</th>\n",
              "      <th>person</th>\n",
              "      <th>gender</th>\n",
              "      <th>number</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <td>0</td>\n",
              "      <td>10001</td>\n",
              "      <td>4</td>\n",
              "      <td>237</td>\n",
              "      <td>М</td>\n",
              "      <td>79602336578</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>1</td>\n",
              "      <td>10002</td>\n",
              "      <td>4</td>\n",
              "      <td>355</td>\n",
              "      <td>Ж</td>\n",
              "      <td>79602336578</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>2</td>\n",
              "      <td>10003</td>\n",
              "      <td>5</td>\n",
              "      <td>211</td>\n",
              "      <td>Ж</td>\n",
              "      <td>79802931277</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <td>3</td>\n",
              "      <td>10004</td>\n",
              "      <td>5</td>\n",
              "      <td>198</td>\n",
              "      <td>М</td>\n",
              "      <td>79802931277</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "      id  class  person gender       number\n",
              "0  10001      4     237      М  79602336578\n",
              "1  10002      4     355      Ж  79602336578\n",
              "2  10003      5     211      Ж  79802931277\n",
              "3  10004      5     198      М  79802931277"
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "#Должно получиться вот так"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "llCOSYBTqoY_"
      },
      "outputs": [],
      "source": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.4"
    },
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "EvQZ-WoeqoY7"
      ],
      "include_colab_link": true
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}